﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class HandSoapDispenserRemarkRepo
    {
        public const string m_msg_head = "HandSoapDispenserRemark";

        public IEnumerable<HandSoapDispenserRemarkModels> GetHandSoapDispenserRemark()
        {
            List<HandSoapDispenserRemarkModels> HandSoapDispenserRemarks = new List<HandSoapDispenserRemarkModels>();
            string SQLCommand = @"select TBHandSoapDispenserRemark.*,TBTitleHandSoapDispenser.TitleHandSoapDispenserName from TBHandSoapDispenserRemark LEFT JOIN TBTitleHandSoapDispenser 
                                           ON TBHandSoapDispenserRemark.TitleHandSoapDispenserId = TBTitleHandSoapDispenser.TitleHandSoapDispenserId
                                           WHERE TBHandSoapDispenserRemark.Status <> 'Delete' ORDER BY TBHandSoapDispenserRemark.TitleHandSoapDispenserId";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        HandSoapDispenserRemarkModels HandSoapDispenserRemark = new HandSoapDispenserRemarkModels();
                        HandSoapDispenserRemark.Id = (int)reader["Id"];
                        HandSoapDispenserRemark.TitleHandSoapDispenserId = (int)reader["TitleHandSoapDispenserId"];
                        HandSoapDispenserRemark.TitleHandSoapDispenserName = (string)reader["TitleHandSoapDispenserName"];
                        HandSoapDispenserRemark.HandSoapDispenserRemarkTh = (string)reader["HandSoapDispenserRemarkTh"];
                        HandSoapDispenserRemark.HandSoapDispenserRemarkEng = (string)reader["HandSoapDispenserRemarkEng"];
                        HandSoapDispenserRemark.Status = (string)reader["Status"];
                        HandSoapDispenserRemark.CreateBy = (string)reader["CreateBy"];
                        HandSoapDispenserRemark.CreateOn = (DateTime)reader["CreateOn"];
                        HandSoapDispenserRemark.UpdateBy = (string)reader["UpdateBy"];
                        HandSoapDispenserRemark.UpdateOn = (DateTime)reader["UpdateOn"];

                        HandSoapDispenserRemarks.Add(HandSoapDispenserRemark);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return HandSoapDispenserRemarks.ToList();
        }

        public HandSoapDispenserRemarkModels GetHandSoapDispenserRemarkById(int id)
        {
            HandSoapDispenserRemarkModels HandSoapDispenserRemark = new HandSoapDispenserRemarkModels();
            string SQLCommand = @"select TBHandSoapDispenserRemark.*,TBTitleHandSoapDispenser.TitleHandSoapDispenserName from TBHandSoapDispenserRemark LEFT JOIN TBTitleHandSoapDispenser 
                                           ON TBHandSoapDispenserRemark.TitleHandSoapDispenserId = TBTitleHandSoapDispenser.TitleHandSoapDispenserId
                                           where TBHandSoapDispenserRemark.Id=@Id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@Id", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        HandSoapDispenserRemark.Id = (int)reader["Id"];
                        HandSoapDispenserRemark.TitleHandSoapDispenserId = (int)reader["TitleHandSoapDispenserId"];
                        HandSoapDispenserRemark.TitleHandSoapDispenserName = (string)reader["TitleHandSoapDispenserName"];
                        HandSoapDispenserRemark.HandSoapDispenserRemarkTh = (string)reader["HandSoapDispenserRemarkTh"];
                        HandSoapDispenserRemark.HandSoapDispenserRemarkEng = (string)reader["HandSoapDispenserRemarkEng"];
                        HandSoapDispenserRemark.Status = (string)reader["Status"];
                        HandSoapDispenserRemark.CreateBy = (string)reader["CreateBy"];
                        HandSoapDispenserRemark.CreateOn = (DateTime)reader["CreateOn"];
                        HandSoapDispenserRemark.UpdateBy = (string)reader["UpdateBy"];
                        HandSoapDispenserRemark.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return HandSoapDispenserRemark;
        }

        public bool Create(HandSoapDispenserRemarkModels HandSoapDispenserRemark)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBHandSoapDispenserRemark
                                    values (@TitleHandSoapDispenserId,@HandSoapDispenserRemarkTh,@HandSoapDispenserRemarkEng,@Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleHandSoapDispenserId", HandSoapDispenserRemark.TitleHandSoapDispenserId);
                    db.AddParameter("@HandSoapDispenserRemarkTh", HandSoapDispenserRemark.HandSoapDispenserRemarkTh);
                    db.AddParameter("@HandSoapDispenserRemarkEng", HandSoapDispenserRemark.HandSoapDispenserRemarkEng);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", HandSoapDispenserRemark.CreateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(HandSoapDispenserRemarkModels HandSoapDispenserRemark)
        {
            bool result = true;
            string SQLCommand = @"Update TBHandSoapDispenserRemark 
                                    Set TitleHandSoapDispenserId=@TitleHandSoapDispenserId,
                                        HandSoapDispenserRemarkTh=@HandSoapDispenserRemarkTh,
                                        HandSoapDispenserRemarkEng=@HandSoapDispenserRemarkEng,
                                        UpdateBy=@UserId,UpdateOn=getdate()
                                    where Id=@Id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleHandSoapDispenserId", HandSoapDispenserRemark.TitleHandSoapDispenserId);
                    db.AddParameter("@HandSoapDispenserRemarkTh", HandSoapDispenserRemark.HandSoapDispenserRemarkTh);
                    db.AddParameter("@HandSoapDispenserRemarkEng", HandSoapDispenserRemark.HandSoapDispenserRemarkEng);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", HandSoapDispenserRemark.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(HandSoapDispenserRemarkModels HandSoapDispenserRemark)
        {
            bool result = true;
            string SQLCommand = @"Update TBHandSoapDispenserRemark
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where Id=@Id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Id", HandSoapDispenserRemark.Id);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", HandSoapDispenserRemark.UpdateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}